﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MVCTutorial.Core.Model;
using System.Data.SQLite;
using System.Data;

namespace MVCTutorial.Core.Impls.Repository.SQLite
{
	internal class SQLiteSkillRepository
		: SQLiteBaseRepository<Skill>, ISkillRepository
	{
		public void Add(Skill entity)
		{
			try
			{
				if (connection.State != ConnectionState.Open)
					connection.Open();

				SQLiteCommand cmd = connection.CreateCommand();

				cmd.CommandText = "insert into skill (description) values(@description); select last_insert_rowid()";
				cmd.Parameters.AddWithValue("description", entity.Description);
				entity.Id = Convert.ToInt32(cmd.ExecuteScalar());
			}
			finally
			{
				connection.Close();
			}
		}

		public void AttachToUser(int skillId, int userId)
		{
			try
			{
				if (connection.State != ConnectionState.Open)
					connection.Open();

				SQLiteCommand cmd = connection.CreateCommand();

				cmd.CommandText = "insert into userSkill (userId, skillId) values(@userId, @skillId)";

				cmd.Parameters.AddWithValue("userId", userId);
				cmd.Parameters.AddWithValue("skillId", skillId);
				cmd.ExecuteNonQuery();
			}
			finally
			{
				connection.Close();
			}
		}

		public void DetachFromUser(int skillId, int userId)
		{
			try
			{
				if (connection.State != ConnectionState.Open)
					connection.Open();

				SQLiteCommand cmd = connection.CreateCommand();

				cmd.CommandText = "delete from userSkill where userId = @userId and skillId = @skillId";

				cmd.Parameters.AddWithValue("userId", userId);
				cmd.Parameters.AddWithValue("skillId", skillId);
				cmd.ExecuteNonQuery();
			}
			finally
			{
				connection.Close();
			}
		}

		public void Remove(int id)
		{
			try
			{
				if (connection.State != ConnectionState.Open)
					connection.Open();

				SQLiteCommand cmd = connection.CreateCommand();

				cmd.CommandText = "delete from skill where skillId = @skillId";

				cmd.Parameters.AddWithValue("userId", id);
				cmd.ExecuteNonQuery();
			}
			finally
			{
				connection.Close();
			}
		}

		public void Update(Skill entity)
		{
			try
			{
				if (connection.State != ConnectionState.Open)
					connection.Open();

				SQLiteCommand cmd = connection.CreateCommand();

				cmd.CommandText = "update skill set description = @description where skillId = @skillId";

				cmd.Parameters.AddWithValue("description", entity.Description);
				cmd.Parameters.AddWithValue("userId", entity.Id);
				cmd.ExecuteNonQuery();
			}
			finally
			{
				connection.Close();
			}
		}

		public Skill GetById(int id)
		{
			try
			{
				if (connection.State != ConnectionState.Open)
					connection.Open();

				SQLiteCommand cmd = connection.CreateCommand();

				cmd.CommandText = "select * from skill where skillId = @skillId";

				cmd.Parameters.AddWithValue("skillId", id);

				SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);

				DataTable table = new DataTable();

				adapter.Fill(table);

				if (table.Rows.Count > 0)
					return RowToObject(table.Rows[0]);

				return null;
			}
			finally
			{
				connection.Close();
			}
		}

		public IEnumerable<Skill> GetAll()
		{
			try
			{
				if (connection.State != ConnectionState.Open)
					connection.Open();

				SQLiteCommand cmd = connection.CreateCommand();

				cmd.CommandText = "select * from skill";

				SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);

				DataTable table = new DataTable();

				adapter.Fill(table);

				foreach (DataRow row in table.Rows)
					yield return RowToObject(row);
			}
			finally
			{
				connection.Close();
			}
		}

		public IEnumerable<Skill> GetForUser(int userId)
		{
			try
			{
				if (connection.State != ConnectionState.Open)
					connection.Open();

				SQLiteCommand cmd = connection.CreateCommand();

				cmd.CommandText = "select s.* from skill s inner join userSkill us on s.skillId = us.skillId where us.userId = @userId";
				
				cmd.Parameters.AddWithValue("userId", userId);

				SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);

				DataTable table = new DataTable();

				adapter.Fill(table);

				foreach (DataRow row in table.Rows)
					yield return RowToObject(row);
			}
			finally
			{
				connection.Close();
			}
		}

		protected override Skill RowToObject(DataRow row)
		{
			return new Skill(Convert.ToInt32(row["skillId"]), row["description"].ToString());
		}
	}
}
